package Server;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelHandler {

	public static List<String> readAllAtSheet(int sheetIndex, String excelDataPath) {
		List<String> list = new ArrayList<String>();
		FileInputStream fis = null;
		Workbook workbook = null;
		try {
			fis = new FileInputStream(excelDataPath);
			ExcelFormat format = getExcelFormat(excelDataPath);
			switch(format) {
			case XLS : 
				workbook = new HSSFWorkbook(fis);
				readAllAtSheet(sheetIndex, workbook, list);
				break;
			case XLSX : 
				workbook = new XSSFWorkbook(fis);
				readAllAtSheet(sheetIndex, workbook, list);
				break;
			default : 
				break;
			}
			
			if(workbook != null) workbook.close();
			if(fis != null) fis.close();
		} catch(IOException e) {
			e.printStackTrace();
		} 
		
		return list;
	}
	
	private static void readAllAtSheet(int sheetIndex, Workbook workbook, List<String> list) {
		int sheetCount = workbook.getNumberOfSheets();
		if(sheetIndex < 0 || sheetIndex >= sheetCount)
			return;
		Sheet sheet = workbook.getSheetAt(sheetIndex);
		Iterator<Row> rowItor = sheet.rowIterator();
		while(rowItor.hasNext()) {
			Row row = rowItor.next();
			StringBuilder body = new StringBuilder();
			Iterator<Cell>  cellItor = row.cellIterator();
			while(cellItor.hasNext()) {
				Cell cell = cellItor.next();
				if(body.length() == 0)
					body.append(getCellValue(cell));
				else 
					body.append("	" + getCellValue(cell));
			}
			list.add(body.toString());
		}
	}
	
	public static List<String> readColsAtSheet(int[] cols, int sheetIndex, String excelDataPath) {
		
		List<String> list = new ArrayList<String>();
		FileInputStream fis = null;
		Workbook workbook = null;
		try {
			fis = new FileInputStream(excelDataPath);
			ExcelFormat format = getExcelFormat(excelDataPath);
			switch(format) {
			case XLS : 
				workbook = new HSSFWorkbook(fis);
				readColsAtSheet(cols, sheetIndex, workbook, list);
				break;
			case XLSX : 
				workbook = new XSSFWorkbook(fis);
				readColsAtSheet(cols, sheetIndex, workbook, list);
				break;
			default : 
				break;
			}
			
			if(workbook != null) workbook.close();
			if(fis != null) fis.close();
		} catch(IOException e) {
			e.printStackTrace();
		} 
		
		return list;
	}
	
	private static void readColsAtSheet(int[] cols, int sheetIndex, Workbook workbook, List<String> list) {
		int sheetCount = workbook.getNumberOfSheets();
		if(sheetIndex < 0 || sheetIndex >= sheetCount)
			return;
		Sheet sheet = workbook.getSheetAt(sheetIndex);
		Iterator<Row> rowItor = sheet.rowIterator();
		
		while(rowItor.hasNext()) {
			Row row = rowItor.next();
			StringBuilder body = new StringBuilder();
			for(int c : cols) {
				Cell cell = row.getCell(c);
				if(body.length() == 0)
					body.append(getCellValue(cell));
				else 
					body.append("++" + getCellValue(cell));
			}
			list.add(body.toString());
		}
	}
	
	
	public static List<String> readRowsAtSheet(int[] rows, int sheetIndex, String excelDataPath) {
		List<String> list = new ArrayList<String>();
		FileInputStream fis = null;
		Workbook workbook = null;
		try {
			fis = new FileInputStream(excelDataPath);
			ExcelFormat format = getExcelFormat(excelDataPath);
			switch(format) {
			case XLS : 
				workbook = new HSSFWorkbook(fis);
				readRowsAtSheet(rows, sheetIndex, workbook, list);
				break;
			case XLSX : 
				workbook = new XSSFWorkbook(fis);
				readRowsAtSheet(rows, sheetIndex, workbook, list);
				break;
			default : 
				break;
			}
			
			if(workbook != null) workbook.close();
			if(fis != null) fis.close();
		} catch(IOException e) {
			e.printStackTrace();
		} 
		
		return list;
	}
	
	private static void readRowsAtSheet(int[] rows, int sheetIndex, Workbook workbook, List<String> list) {
		int sheetCount = workbook.getNumberOfSheets();
		if(sheetIndex < 0 || sheetIndex >= sheetCount)
			return;
		Sheet sheet = workbook.getSheetAt(sheetIndex);
		for(int r : rows) {
			Row row = sheet.getRow(r);
			StringBuilder body = new StringBuilder();
			Iterator<Cell> cellItor = row.cellIterator();
			while(cellItor.hasNext()) {
				Cell cell = cellItor.next();
				if(body.length() == 0)
					body.append(getCellValue(cell));
				else 
					body.append("	" + getCellValue(cell));
			}
			list.add(body.toString());
		}
	}
	
	private static String getCellValue(Cell cell) {
		String value = "NULL";
		if(cell == null)
			return value;
		
		int cellType = cell.getCellType();
		switch(cellType) {
		case Cell.CELL_TYPE_STRING:
			if(!cell.getStringCellValue().equals(""))
				value = cell.getStringCellValue().trim().replaceAll(" ", "");
			break;
		case Cell.CELL_TYPE_NUMERIC:
			cell.setCellType(Cell.CELL_TYPE_STRING);
			if(!cell.getStringCellValue().equals(""))
				value = cell.getStringCellValue().trim().replaceAll(" ", "");
			break;
		case Cell.CELL_TYPE_FORMULA:
			cell.setCellType(Cell.CELL_TYPE_STRING);
			if(!cell.getStringCellValue().equals(""))
				value = cell.getStringCellValue().trim().replaceAll(" ", "");
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			break;
		case Cell.CELL_TYPE_BLANK:
			break;
		case Cell.CELL_TYPE_ERROR:
			break;
		}
		return value.replaceAll("\r|\n|\t", "");
	}
	
	
	private static ExcelFormat getExcelFormat(String filePath) {
		if(filePath.matches("^.+\\.(?i)(xls)$")) 
			return ExcelFormat.XLS;
		else if(filePath.matches("^.+\\.(?i)(xlsx)$"))
			return ExcelFormat.XLSX;
		else 
			return ExcelFormat.OTHERS;
	}
	
	enum ExcelFormat {
		XLS, XLSX, OTHERS
	}
	
	public static void  ExcelOut(String infilepath,String outpath) {
		XSSFWorkbook work = new XSSFWorkbook();
		Sheet sheet = work.createSheet();
		
		try {
			BufferedReader Reader = new BufferedReader(
					new InputStreamReader(new FileInputStream(infilepath),"utf-8"));
			String line = Reader.readLine();
			System.out.print(line);
			int colnum = Integer.parseInt(line);
			int[] cols = new int[colnum];
			for(int i=0;i<colnum;i++)
				cols[i] = i;
			
			int rows = 0;
			while((line = Reader.readLine())!=null){
				if(line.trim().equals("")) continue;
				
				String[] temp = line.split("\t");
				if(temp.length != colnum) {System.out.print("error\n");break;}
				
				Row row = sheet.createRow(rows++);
				Cell cell = null;
				for(int i=0;i<colnum;i++){
					cell = row.createCell(i);
					cell.setCellValue(temp[i]);
				}
			}
			
			Reader.close();
		} catch (IOException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		try  
        {  
            FileOutputStream fout = new FileOutputStream(outpath);  
            work.write(fout);  
            fout.close(); 
            work.close();
        }  
        catch (Exception e)  
        {  
            e.printStackTrace();  
        } 
		
		
	}
	public static void main(String[] args) {
		String excelname = "D:/workspace/HotEventMonitoring/2017年1月-2017年12月嘉兴的单子信息/2017年1月-2017年6月嘉兴的单子信息.xlsx";
		String txtname = "D:/workspace/HotEventMonitoring/2017年1月-2017年12月嘉兴的单子信息/1-6月.txt";
		int[] cols={1,2,10};
		List<String> excelread = ExcelHandler.readColsAtSheet(cols, 0,excelname);	
		IOHandler.serialize2File(excelread, new File(txtname));
	}
}
